/*
 * Member DAO.java
 *
 * Created on February 13, 2007
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

/**
 *
 * @author craig marshall
 */
package finalintex2web;
import java.util.*;
import java.io.*;
import java.sql.*;

/**
 * database access object for mambers
 */
public class MemberDAO {
    
    
    ///////////////////////////////////////
    ///   Singleton pattern
    
    private static MemberDAO instance = null;
    
    /** Creates a new instance of RSSFeedDAO */
    private MemberDAO() {
    }
    
    /**
     * 
     * @return 
     */
    public static synchronized MemberDAO getInstance() {
        if (instance == null) {
            instance = new MemberDAO();
        }
        return instance;
    }
    
    
    /////////////////////////////////
    ///   CREATE
    
    /**
     * 
     * @return 
     */
    public MemberBO create() {
        String id = GUID.generate();
        MemberBO memb = new MemberBO(id);
        Cache c = Cache.getInstance();
        c.put(memb.getId(), memb);
        return memb;
    }
    
    
    /////////////////////////////////////
    ///   READ
    /** 
     * This is the public read statement.  It loads an existing record
     * from the database.
     */
    public synchronized MemberBO read(String id) throws DataException, ConnectionPoolException {
        if(Cache.getInstance().containsKey(id)){
            MemberBO memb = (MemberBO)Cache.getInstance().get(id);
            return memb;
        }    
        
        try {
            Connection conn = ConnectionPool.getInstance().get();
            MemberBO memb = read(id,conn);
            ConnectionPool.getInstance().release(conn);
            return memb;
        }catch (SQLException e) {
            // rollback
            e.printStackTrace();
            throw new DataException("Could not retrieve member record for id=" + id, e);
        }
        
       
    }
    
    /** 
     *  This is a package method that is called by the public read (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection MemberBOs or SQLExceptions.
     */
    public synchronized MemberBO read(String id, Connection conn) throws SQLException, DataException,ConnectionPoolException  {
            if(Cache.getInstance().containsKey(id)){
            MemberBO memb = (MemberBO)Cache.getInstance().get(id);
            return memb;
            } 
                
                   
           PreparedStatement stmt = conn.prepareStatement("SELECT * FROM Member WHERE id=?");
           stmt.setString(1,id);
           ResultSet rs =  stmt.executeQuery();
           
           ConnectionPool.getInstance().release(conn);
            
            if (rs.next()) {
                MemberBO memb = new MemberBO(id);
                memb.setCreditcard(rs.getString("creditcard"));
                memb.setStartdate(rs.getString("startdate"));
                memb.setEnddate(rs.getString("enddate"));
                Cache.getInstance().put(id,memb);
            
                //get area of interests
                PreparedStatement stmt2 = conn.prepareStatement("SELECT a.id, description FROM Member m, areaofinterest a WHERE m.id=?");
                stmt2.setString(1,id);
                ResultSet rs2 =  stmt2.executeQuery();
                ArrayList<AreaOfInterestBO> areaofinterest = new ArrayList<AreaOfInterestBO>();
                while (rs2.next()){
                    AreaOfInterestBO aoi = new AreaOfInterestBO(rs.getString("id"));
                    aoi.setDescription(rs2.getString("description"));
                    areaofinterest.add(aoi);
                    aoi.setIndb(true);
                }
                memb.setAreaofinterest(areaofinterest);
                stmt.close();
                stmt2.close();
                
                //maybe set customer
              
                memb.setIndb(true);
                
                return memb;

            }else{
                throw new DataException("MemberBO was not found in the database.");

            }
            
            
    }
        
    
    
    //////////////////////////////////
    ///   UPDATE
    
    /** 
     * This is the public save method.  It is what is called when
     * the user (controller) code wants to save or update an MemberBO
     * into the database.
     */
    public synchronized void save(MemberBO memb) throws DataException,ConnectionPoolException {
        
      
        try {
            Connection conn = ConnectionPool.getInstance().get();
            save(memb,conn);
            ConnectionPool.getInstance().release(conn);
        }catch (SQLException e) {
            //rollback
            throw new DataException("Could not retrieve record for id=" + memb.getId(), e);
        }
        
        // release the connection back to the pool
        return;
    }
    
    /** 
     *  This is a package method that is called by the public save (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection MemberBOs or SQLExceptions.
     *
     *  By having other DAOs call this save method (rather than update or
     *  insert below, each DAO in a chained save (like the MemberDAO calling
     *  MembershipDAO calling InterestDAO save chain) can independently decide
     *  whether to udpate or insert the BO it's saving.  That's why I made
     *  update and insert private rather than package level -- if you call
     *  them directly from another DAO, this DAO can't decide whether it's
     *  MemberBO needs to be inserted or updated.
     */
     synchronized void save(MemberBO memb, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        // check the dirty flag in the MemberBO.  if it is dirty, 
        // run update or insert
      
        if (memb.isDirty()) {
            if (memb.isIndb()) {
                update(memb, conn);
            }else{
                insert(memb, conn);
            }
            memb.setDirty(false);
        }
        
        // call save(bo, conn) on any subMemberBOs (like MemberDAO to MembershipDAO)
        // touch the cache for the MemberBO
        if(memb.getAreaofinterest() != null){
            
            int size = memb.getAreaofinterest().size();
            for(int z=0; z < 1; z++){
      
            AreaOfInterestDAO.getInstance().save(memb.getAreaofinterest().get(z),conn);
            PreparedStatement stmt = conn.prepareStatement("INSERT INTO memberareaofinterest (member,areaofinterest,id) VALUES ( ?,?,?)");
            stmt.setString(1,memb.getId());
            stmt.setString(2,memb.getAreaofinterest().get(z).getId());
            stmt.setString(3,GUID.generate());
            stmt.execute();
            stmt.close();
            Cache.getInstance().touch(memb.getAreaofinterest().get(z).getId());
            ConnectionPool.getInstance().release(conn);
            }
       }
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     */
    private synchronized void update(MemberBO memb, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        PreparedStatement stmt = conn.prepareStatement("UPDATE member  SET creditcard=?, startdate=?, enddate=? WHERE id=? ");
        stmt.setString(1,memb.getCreditcard());
        stmt.setString(2,memb.getStartdate());
        stmt.setString(3,memb.getEnddate());
        stmt.setString(4,memb.getId());
        stmt.execute();
        stmt.close();
        ConnectionPool.getInstance().release(conn);
    }
     
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL insert statement and make it more readable.  But
     * logically, it's really just a part of save.
     */
    private synchronized void insert(MemberBO memb, Connection conn) throws SQLException, DataException {
        // do the insert SQL statement
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO Member (creditcard,startdate,enddate,id) VALUES ( ?,?,?,?)");
       stmt.setString(1,memb.getCreditcard());
        stmt.setString(2,memb.getStartdate());
        stmt.setString(3,memb.getEnddate());
        stmt.setString(4,memb.getId());
        stmt.execute();
        stmt.close();
        // tell the MemberBO that it's now in the db (so we call update next time not insert)
        memb.setIndb(true);
    }
    
    
    ////////////////////////////////////
    ///   DELETE
    // We are not implementing this feature in this class to help preserve data intergity.

    //////////////////////////////
    ///  SEARCH methods
    
    /**
     * 
     * @param creditcard 
     * @throws intex2.DataException 
     * @throws intex2.ConnectionPoolException 
     * @throws java.sql.SQLException 
     * @return 
     */
     public ArrayList<String> getByCreditcard(String creditcard) throws DataException,ConnectionPoolException,SQLException {
        
         ArrayList<String> memb = new ArrayList<String>();
         Connection conn = ConnectionPool.getInstance().get();
         PreparedStatement stmt = conn.prepareStatement("SELECT c.id, firstname, lastname FROM customer c,member m WHERE c.membership = m.id and creditcard = ?" );
         stmt.setString(1,creditcard);
         ResultSet rs = stmt.executeQuery();
         
        while(rs.next()){
           memb.add(rs.getString("id"));
           memb.add(rs.getString("firstname"));
           memb.add(rs.getString("lastname"));
        }
        stmt.close();
        ConnectionPool.getInstance().release(conn);      
       
        return memb;
    }

}